from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `idcard_info` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `created_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL  DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `name` VARCHAR(50) NOT NULL  COMMENT '姓名',
    `idcard_number` VARCHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
    `gender` VARCHAR(10)   COMMENT '性别',
    `ethnicity` VARCHAR(20)   COMMENT '民族',
    `birth_date` VARCHAR(50)   COMMENT '出生日期',
    `address` VARCHAR(255)   COMMENT '地址',
    `issuing_authority` VARCHAR(100)   COMMENT '签发机关',
    `valid_period` VARCHAR(100)   COMMENT '有效期限',
    `raw_ocr_data` JSON   COMMENT '原始OCR识别数据',
    `user_id` INT NOT NULL COMMENT '关联用户',
    CONSTRAINT `fk_idcard_i_users_e932e0fd` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    KEY `idx_idcard_info_user_id_d9f203` (`user_id`),
    KEY `idx_idcard_info_idcard__ec63db` (`idcard_number`)
) CHARACTER SET utf8mb4 COMMENT='身份证信息表';"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        DROP TABLE IF EXISTS `idcard_info`;"""
